package status;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;



public class Status {

	StatusVo vo;

	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@192.168.10.122:1521:XE";
	String db_user = "jobs";
	String db_pw = "1234";

	Connection conn;

	//페이지 분리 관련된 필드
	private  int listSize = 10; // 한페이지에 보여질 행수
	private	 int blockSize = 2; //보여질 페이지 이동 번호 수.( 1 2 )
	private	 int totSize = 0; // 검색된 데이터 총 갯수
	private	 int totPage = 0; // 전체 페이지 수
	private	 int totBlock = 0; // 전체 블럭수
	private	 int nowPage = 1; //현재 페이지
	private	 int nowBlock = 1; //현재 블럭
	private	 int startNo =1; //목록의 첫번째 위치
	private	 int endNo =3; //목록의 끝위치
	private	 int startPage = 1; //현재 블럭의 시작 페이지 번호
	private	 int endPage = 2;// 현재 블럭의 끝 페이지 번호


	public Status() {
		try {
			//1)드라이버 로딩
			Class.forName(driver).newInstance();
			System.out.println("드라이버가 로딩됨");

			//2)Connection 생성
			conn = DriverManager.getConnection(url,db_user,db_pw);
			System.out.println("Connection good!");

		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	//11개의 변수를 핸들링
	public void computePage(){
		totPage = (int)Math.ceil(totSize*1.0 / listSize); // 기본적으로  math는 반환타입이 long이라 1.0 곱해줘야된다.
		totBlock = (int)Math.ceil(totPage*1.0/blockSize);

		nowBlock = (int)Math.ceil(nowPage*1.0/blockSize); 

		endPage = nowBlock * blockSize; 
		startPage = endPage - blockSize+1;
		if(totPage < endPage) endPage = totPage; 

		endNo = nowPage * listSize;
		startNo = endNo -listSize+1;
		if(totSize < endNo) endNo = totSize;


	}


	public Vector<StatusVo> list(){

		Vector<StatusVo> v = new Vector<>();
		String sql="";
		PreparedStatement ps=null;
		ResultSet rs = null;
		try {
			sql = " select count(serial) cnt from status "
					+ " where  (mid like ? or content like ? or subject like ?) ";
					

			ps = conn.prepareStatement(sql);
			ps.setString(1, "%"+vo.getFind()+"%");
			ps.setString(2, "%"+vo.getFind()+"%");
			ps.setString(3, "%"+vo.getFind()+"%");
			
			rs = ps.executeQuery();
			rs.next();

			System.out.println("cnt=" + rs.getInt("cnt"));//전체 건수 표시 

			totSize = rs.getInt("cnt");
			computePage();
			
			sql = "select * from ("
					+ "    select rownum no, a.* from( " 
					+ "     select * from status where (mid like ? or content like ? or subject like ?) "
					+ "      order by serial desc ) a "
					+ " ) where no between ? and ? ";
			
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%"+vo.getFind()+"%");
			ps.setString(2, "%"+vo.getFind()+"%");
			ps.setString(3, "%"+vo.getFind()+"%");
			ps.setInt(4, startNo);
	        ps.setInt(5, endNo);
			
			
			rs = ps.executeQuery();
			while(rs.next()){

				StatusVo gv = new StatusVo();
				gv.setSerial(rs.getInt("serial"));
				gv.setMid(rs.getString("mid"));
				gv.setCompany(rs.getString("company"));
				gv.setSubject(rs.getString("subject"));
				gv.setContent(rs.getString("content"));
				gv.setMdate(rs.getString("mdate"));
				gv.setHit(rs.getInt("hit"));

				v.add(gv);


			}
			rs.close();
			ps.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		}



		return v;
	}

	public boolean insert(){
		boolean tflag = false;

		try{


			String sql = "insert into status(serial, company, mid, subject, content, mdate, hit)values(st_seq.nextval,?,?,?,?,sysdate,?)";

			PreparedStatement ps = conn.prepareStatement(sql);

			ps.setString(1, vo.getCompany());
			ps.setString(2, vo.getMid());
			ps.setString(3, vo.getSubject());
			ps.setString(4, vo.getContent());
			ps.setInt(5, vo.getHit());

			int r = ps.executeUpdate();

			if(r>0){
				tflag = true;

			}
		}catch(Exception ex){
			ex.printStackTrace();
		}

		return tflag;
	}

	public StatusVo view(){
		StatusVo gv = new StatusVo();
		String sql="";
		PreparedStatement ps = null;

		try {

			sql=" update status set hit = hit+1 where serial="+ vo.getSerial();
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();


			sql="select * from status where serial=? ";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());

			ResultSet rs = ps.executeQuery();

			if(rs.next()){


				gv.setSerial(rs.getInt("serial"));
				gv.setMid(rs.getString("mid"));
				gv.setCompany(rs.getString("company"));
				gv.setSubject(rs.getString("subject"));
				gv.setContent(rs.getString("content"));
				gv.setMdate(rs.getString("mdate"));
				gv.setHit(rs.getInt("hit"));


			}
			rs.close();
			ps.close();
			conn.close();

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return gv;
	}
	public boolean modify(){
		boolean flag = false;

		String sql = "update status set subject=? , content=? , company=? where serial=? ";

		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, vo.getSubject());
			ps.setString(2, vo.getContent());
			ps.setString(3, vo.getCompany());
			ps.setInt(4, vo.getSerial());

			int r = ps.executeUpdate();

			if(r>0){
				flag = true;

			}

		}catch(Exception ex){
			ex.printStackTrace();
		}

		return flag;
	}

	public boolean delete(){
		boolean flag = false;

		String sql = "delete from status where serial=?";

		try{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, vo.getSerial());

			int r = ps.executeUpdate();

			if(r>0){
				flag = true;

			}
			ps.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}

		return flag;
	}
	public StatusVo getVo() {
		return vo;
	}

	public void setVo(StatusVo vo) {
		this.vo = vo;
	}

	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}



}
